﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using SYcwpb.Helpers;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using SYcwpb.Models;

namespace SYcwpb.Access
{
    class CrossingRoadAccess
    {
        SqlDataLayer sqlHelper = new SqlDataLayer();

        /// <summary>
        /// 获取早班的出库车
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<string> GetMorningShiftTrain(string lastId,string changeZb,string tableName)
        {
            if (string.IsNullOrEmpty(lastId) || string.IsNullOrEmpty(changeZb) || string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            int int_time = Convert.ToInt32(changeZb);

            ObservableCollection<string> List = new ObservableCollection<string>();
            string sql_getMorningTrain = "select distinct TrainID from [" + tableName + "] where (Station = 66 or Station = 67) and LastID ='" + lastId + "' and Depart < " + int_time; 
            DataTable result = sqlHelper.SelectDt(sql_getMorningTrain);
            if (null != result || result.Rows.Count > 0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    List.Add(result.Rows[i]["TrainID"].ToString());
                }
            }
            return List;
        }

        //public ObservableCollection<string> GetDayTrains(string changeZb, string changeBy, string tableName)
        //{
        //    if (string.IsNullOrEmpty(changeZb) || string.IsNullOrEmpty(changeBy) || string.IsNullOrEmpty(tableName))
        //    {
        //        return null;
        //    }
        //    int int_time_begin = Convert.ToInt32(changeZb);
        //    int int_time_end = Convert.ToInt32(changeBy);

        //    ObservableCollection<string> List = new ObservableCollection<string>();
        //    string sql_getDayTrain = "";
        //    DataTable result = sqlHelper.SelectDt(sql_getDayTrain);
        //    if (null != result || result.Rows.Count > 0)
        //    {
        //        for (int i = 0; i < result.Rows.Count; i++)
        //        {
        //            List.Add(result.Rows[i]["TrainID"].ToString());
        //        }
        //    }
        //    List.Insert(0, "");

        //    return List;
        //}

        /// <summary>
        /// 获取夜班回库车次
        /// </summary>
        /// <param name="changeBy">白班、夜班交接班时间</param>
        /// <param name="?"></param>
        /// <returns></returns>
        public ObservableCollection<string> GetNightBackTrain(string changeBy, string tableName)
        {
            if (string.IsNullOrEmpty(changeBy) || string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            int int_time = Convert.ToInt32(changeBy);

            ObservableCollection<string> List = new ObservableCollection<string>();
            string sql_getNightBackTrain = "select distinct TrainID,* from [" + tableName + "] where arc_ne = '#####' and Arrive > " + changeBy + " order by MidID ";
            DataTable result = sqlHelper.SelectDt(sql_getNightBackTrain);
            if (null != result || result.Rows.Count > 0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    List.Add(result.Rows[i]["TrainID"].ToString());
                }
            }
            List.Insert(0, "");

            return List;
        }

        /// <summary>
        /// 获取白班的出库车
        /// </summary>
        /// <param name="changeZB">早班，白班交接班时间</param>
        /// <param name="bbOutTime">白班开出时间</param>
        /// <param name="lastId">首车次的LastID</param>
        /// <param name="tableName">时刻表名称</param>
        /// <returns></returns>
        public ObservableCollection<string> GetBBFirstOutTrains(string changeZB,int bbOutTime,string lastId,string tableName)
        {
            if (string.IsNullOrEmpty(changeZB) || string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            int int_time_zb = Convert.ToInt32(changeZB);

            ObservableCollection<string> List = new ObservableCollection<string>();
            string sql_getBBFirstOutTrains = "select distinct TrainID from [" + tableName + "] where (Station = 66 or Station = 67 ) and LastID = '" + lastId + "' and Depart between " + int_time_zb + " and " + bbOutTime;
            DataTable result = sqlHelper.SelectDt(sql_getBBFirstOutTrains);
            if (null != result || result.Rows.Count > 0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    List.Add(result.Rows[i]["TrainID"].ToString());
                }
            }
            List.Insert(0, "");

            return List;
        }

        /// <summary>
        /// 该时刻表对应所有车次的集合，包括 车次号，发车时间，到达换班站的时间，结束时间。用于交路表编辑
        /// </summary>
        public ObservableCollection<SmallTrain> GetAllTrain(string tableid,string tableName)
        {
            ObservableCollection<SmallTrain> list = new ObservableCollection<SmallTrain>();

            string sql_get = "select distinct A.TrainID,A.LastID,B.TBegin,A.Arrive,B.TEnd, DID from [" + tableName + "]" +
                            " A left join TrainDetailInfo B on A.TrainID = B.TrainID  where A.Station = '10' and B.tableInfo_id = '" + tableid + "' order by TBegin";
            DataTable resultDT = sqlHelper.SelectDt(sql_get);
            if (resultDT == null || resultDT.Rows.Count < 1)
                return list;

            for (int i = 0; i < resultDT.Rows.Count; i ++)
            {
                SmallTrain train = new SmallTrain();

                train.TrainID = resultDT.Rows[i]["TrainID"].ToString();
                train.BeginTime = Convert.ToInt32(resultDT.Rows[i]["TBegin"]);
                train.TimeAtChangePoint = Convert.ToInt32(resultDT.Rows[i]["Arrive"].ToString());
                train.EndTime = Convert.ToInt32(resultDT.Rows[i]["TEnd"].ToString());

                list.Add(train);
            }

            return list;
        }

        /// <summary>
        /// 检查是否已存在该时刻表对应的交路表
        /// </summary>
        /// <param name="timeTableID">时刻表ID</param>
        /// <returns></returns>
        public bool CheckHasCrossRoad(string timeTableID)
        {
            string sql_check = "select count(*) tableInfo_id from CrossingRoad where tableInfo_id = '" + timeTableID + "' and isused = 1";
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 返回存在的时刻表对应的交路表
        /// </summary>
        /// <returns></returns>
        public DataTable GetExistCrossingRoad(string timeTableID)
        {
            string sql_get = "select zby,number,grp_type as [type],attenPlace,attenTime,trainTime,reptrain,stringRunTrain,offTrain,bCBackGarageTrainID,midStart,midOut,offTime,offPlace,miles,wHours,notes,lastTrainTime" +
                             " from CrossingRoad left join GroupType on grp_id = GroupType.ID where tableInfo_id = '" + timeTableID + "' and isused = 1 order by zby,number";
            return sqlHelper.SelectDt(sql_get);
        }

        public bool CheckTrainIDExists(string tableName,string TrainID)
        {
            string sql_check = "select count(*) from [" + tableName + "] where TrainID = '" + TrainID + "'";

            int count = Convert.ToInt32(sqlHelper.GetSingle(sql_check));

            return count > 0;
        }

        /// <summary>
        /// 返回 热备，调车，试车线，备用，正线，特殊组对应的ID
        /// </summary>
        /// <returns></returns>
        public DataTable GetGroupType()
        {
            string sql_get = "select * from GroupType";
            return sqlHelper.SelectDt(sql_get);
        }

        /// <summary>
        /// 删除时刻表对应的交路表,设置isued位为 0 
        /// </summary>
        /// <param name="timeTableID">时刻表</param>
        public bool DeleCrossingRoad(string timeTableID)
        {
            string sql_dele = "update CrossingRoad set isused = 0 where tableInfo_id = '" + timeTableID + "'";
            return sqlHelper.ExecuteSql(sql_dele) > 0;
        }

        /// <summary>
        /// 保存交路表的信息
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool SaveNewCrossingRoad(DataTable dt)
        {
            bool result = false;

            try
            {
                using (SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr))
                {
                    bcp.BatchSize = 100;
                    bcp.DestinationTableName = "CrossingRoad";
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        bcp.ColumnMappings.Add(i, (i + 1) );
                    }
                    bcp.WriteToServer(dt);

                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                string err = ex.Message;
            }

            return result;
        }
    }
}
